package nosql.query;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

import org.json.simple.JSONArray;
import org.json.simple.JSONObject;
import org.json.simple.parser.ParseException;

import nosql.MysqlConnector;
import nosql.property.Property;

/**
 * Criteria for =,<=<=,>,>=,<> query.
 * @author Yexi Jiang <http://sites.google.com/site/yexijiang/>
 *
 */
public class ConditionalCriteria extends Criteria {
	
	public static final String EQUAL = "=";
	public static final String SMALLER = "<";
	public static final String SMALLER_EQUAL = "<=";
	public static final String GREATER = ">";
	public static final String GREATER_EQUAL = ">=";
	public static final String UNEQUAL = "<>";
	
	protected String left;
	protected String right;
	protected String condition;
	protected int rightType;
	protected boolean simple;
	
	protected MysqlConnector connector;
	
	static ConditionalCriteria createConditionalCriteria(String str){
		int index = -1;
		String conditionType = null;

		if((index = str.indexOf(UNEQUAL)) != -1)
			conditionType = UNEQUAL;
		else if((index = str.indexOf(SMALLER_EQUAL)) != -1)
			conditionType = SMALLER_EQUAL;
		else if((index = str.indexOf(GREATER_EQUAL)) != -1)
			conditionType = GREATER_EQUAL;
		else if((index = str.indexOf(SMALLER)) != -1)
			conditionType = SMALLER;
		else if((index = str.indexOf(GREATER)) != -1)
			conditionType = GREATER;
		else if((index = str.indexOf(EQUAL)) != -1)
			conditionType = EQUAL;
		
		if(index == -1)
			return null;
		else{
			String left = str.substring(0, index).trim();
			String right = null;
			String condition = null;
			if(conditionType == EQUAL || conditionType == SMALLER || conditionType == GREATER){
				right = str.substring(index + 1).trim();
				condition = str.substring(index, index + 1).trim();
			}
			else{
				right = str.substring(index + 2).trim();
				condition = str.substring(index, index + 2).trim();
			}
			int valueType = Property.UNDEFINED;
			if(right.contains("[")){
				StringBuilder trimedRight = new StringBuilder();
				trimedRight.append("[");
				String[] values = right.substring(1, right.length()-1).split(",");
				for(int i = 0; i < values.length; ++i){
					trimedRight.append(values[i].trim());
					if(i != values.length - 1)
						trimedRight.append(",");
				}
				trimedRight.append("]");
				right = trimedRight.toString();
				valueType = Property.ARRAY;
			}
			else if(right.contains("\"")){
				valueType = Property.STRING;
			}
			else
				valueType = Property.NUMBER;
			
			return new ConditionalCriteria(left, right, condition, valueType);
		}
	}
	
	public ConditionalCriteria(String left, String right, String condition, int rightType){
		this.left = left;
		this.right = right;
		this.rightType = rightType;
		this.condition = condition;
		if(left.contains("."))
			simple = false;
		else
			simple = true;
	}
	
	public boolean isSimple(){
		return simple;
	}
	
	public String getLeft() {
		return left;
	}

	public String getRight() {
		return right;
	}
	
	public int getRightType() {
		return rightType;
	}

	public String getCondition() {
		return condition;
	}

	public void setMysqlConnector(MysqlConnector connector){
		this.connector = connector;
	}
	
	public Set<String> getSatisfyEntities() throws SQLException, CriteriaException{
		Set<String> entitySet = new HashSet<String>();
		//	check whether query table exists in database
		Set<String> propertyTablesSet = connector.getAllPropertyTableName();
		String[] properties = left.split(".");
		if(properties.length == 0 && !propertyTablesSet.contains(left))
			return entitySet;
		else {
			for (String property : properties) {
				if (!propertyTablesSet.contains(property))
					return entitySet;
			}
		}
		
		Statement stmt = connector.getConnection().createStatement();
		if(left.contains(".")){	//	handle embed property query
			int dotIndex = left.indexOf(".");
			String tableName = left.substring(0, dotIndex);
			String sql = "select distinct entityId from " + tableName;
			ResultSet rs = stmt.executeQuery(sql);
			Set<String> allEntitySet = new HashSet<String>();
			while(rs.next())
				allEntitySet.add(rs.getString("entityId"));
			rs.close();
			for(String entityId : allEntitySet){
				String copyName = left.substring(dotIndex + 1);
				String sqlRef = "select ref from " + tableName + " where entityId='" + entityId + "'";
				ResultSet rsResultSet = stmt.executeQuery(sqlRef);
				List<String> jsonStringList = new ArrayList<String>();
				while(rsResultSet.next()){
					jsonStringList.add(rsResultSet.getString("ref"));
				}
				rsResultSet.close();
				for(String jsonString : jsonStringList){	//	filter entityId according to ref
					boolean find = checkValueInJson(copyName, right, jsonString);
					if(find == true)
						entitySet.add(entityId);
				}
			}
		}
		else{	//	handle simple property query
			int rightType = Property.UNDEFINED;
			if(right.contains("\""))
				rightType = Property.STRING;
			else if(right.contains("{") || right.contains("["))
				rightType = Property.COMPLEX;
			String sql = null;
			if(rightType == Property.STRING){
				sql = "select distinct entityId from " + left + " where ref" + condition + "'" + right.substring(1, right.length()-1) + "';";
				ResultSet rs = stmt.executeQuery(sql);
				while(rs.next()){
					entitySet.add(rs.getString("entityId"));
				}
				rs.close();
			}
			else if(rightType == Property.COMPLEX){	//	ignore embed
				return entitySet;
			}
			else{
				if(right.equals("true") || right.equals("false")){
					if(!condition.equals("=") && !condition.equals("<>"))
						throw new CriteriaException("= or <> are only valid condition for boolean type.");
					sql = "select distinct entityId from " + left + " where ref='" + right + "'";
					ResultSet rs = stmt.executeQuery(sql);
					while(rs.next()){
						entitySet.add(rs.getString("entityId"));
					}
					rs.close();
				}
				try{
					double rightValue = Double.parseDouble(right);
					sql = "select distinct entityId, ref from " + left + " where UPPER(ref) = LOWER(ref);"; 
					ResultSet rs = stmt.executeQuery(sql);
					while(rs.next()){
						String ref = rs.getString("ref");
						try{
							double refValue = Double.parseDouble(ref);
							if(condition.equals("=") && refValue == rightValue)
								entitySet.add(rs.getString("entityId"));
							else if(condition.equals("<") && refValue < rightValue)
								entitySet.add(rs.getString("entityId"));
							else if(condition.equals("<=") && refValue <= rightValue)
								entitySet.add(rs.getString("entityId"));
							else if(condition.equals(">") && refValue > rightValue)
								entitySet.add(rs.getString("entityId"));
							else if(condition.equals(">=") && refValue >= rightValue)
								entitySet.add(rs.getString("entityId"));
							else if(condition.equals("<>") && refValue != rightValue)
								entitySet.add(rs.getString("entityId"));
						}catch(NumberFormatException e){
							continue;
						}
					}
				}catch(NumberFormatException e){
					e.printStackTrace();
				}
			}
		}
		return entitySet;
	}
	
	/**
	 * To find out whether there is a value in JSON.
	 * @param tablesChain
	 * @param value
	 * @param jsonString
	 * @return
	 * @throws ParseException 
	 */
	private boolean checkValueInJson(String tablesChain, String value, String jsonString){
		int dotIndex = tablesChain.indexOf(".");
		if(dotIndex == -1){	//	no more embed
			try{
				Object obj = parser.parse(jsonString);
				if(obj instanceof JSONArray){
					int arrIndex = Integer.parseInt(tablesChain);
					String arrIndexValue = ((JSONArray)obj).get(arrIndex).toString();
					if(arrIndexValue.equals(value))
						return true;
					else
						return false;
				}
				else if(obj instanceof JSONObject){
					String mapValue = ((JSONObject)obj).get(tablesChain).toString();
					if(right.contains("\"")){	//	string type
						if(condition.equals("=")){
							if(right.equals(mapValue))
								return true;
							else
								return false;
						}
						else if(condition.equals("<>")){
							if(right.equals(mapValue))
								return false;
							else
								return true;
						}
						else if(condition.equals("<")){
							if(mapValue.compareTo(right) < 0)
								return true;
							else
								return false;
						}
						else if(condition.equals("<=")){
							if(mapValue.compareTo(right) <= 0)
								return true;
							else
								return false;
						}
						else if(condition.equals(">")){
							if(mapValue.compareTo(right) > 0)
								return true;
							else
								return false;
						}
						else if(condition.equals(">=")){
							if(mapValue.compareTo(right) >= 0)
								return true;
							else
								return false;
						}
					}
					else if((right.equals("true") || right.equals("false")) && right.equals(mapValue))
						return true;
					else{
						double refValue = Double.parseDouble(mapValue);
						double dValue = Double.parseDouble(value);
						if(condition.equals("=")){
							if(refValue == dValue)
								return true;
							else
								return false;
						}
						else if(condition.equals("<>")){
							if(refValue != dValue)
								return true;
							else
								return false;
						}
						else if(condition.equals("<")){
							if(refValue < dValue)
								return true;
							else
								return false;
						}
						else if(condition.equals("<=")){
							if(refValue <= dValue)
								return true;
							else
								return false;
						}
						else if(condition.equals(">")){
							if(refValue > dValue)
								return true;
							else
								return false;
						}
						else if(condition.equals(">=")){
							if(refValue >= dValue)
								return true;
							else {
								return false;
							}
						}
					}
				}
				else{	//	basic type
					return false;
				}
			} catch(ParseException e){	//	type String
				if(value.equals(jsonString))
					return true;
			}
		}
		else{	//	more embed
			try{
				Object obj = parser.parse(jsonString);
				String property = tablesChain.substring(0, dotIndex); // get next property
				tablesChain = tablesChain.substring(dotIndex + 1);
				try {
					int index = Integer.parseInt(property);
					if (!(obj instanceof JSONArray))
						return false;
					else {
						String innerJSON = ((JSONArray)obj).get(index).toString();
						return checkValueInJson(tablesChain, value, innerJSON);
					}
				} catch (NumberFormatException e) {
					if (!(obj instanceof JSONObject))
						return false;
					else {
						String innerJSON = ((JSONObject)obj).get(property).toString();
						return checkValueInJson(tablesChain, value, innerJSON);
					}
				}
			}catch(ParseException e){
				e.printStackTrace();
			}
		}
		return false;
	}
}
